clear all
cap close
set more off

cd "."

* ==============================================================================
* CHMDA loan-level origination data (proprietary owned by FRB)
* ==============================================================================

foreach yr of numlist 2007/2017{

import delimited "chmda`yr'.csv",clear

replace county_code=. if county_code<0
replace msa_md=. if msa_md<0
replace state_code=. if state_code<0

keep if property_type==1
keep if occupancy==1
keep if lien_status==1
drop property_type occupancy lien_status

gen state_str = string(state_code,"%02.0f")
gen county_str = string(county_code,"%03.0f")
replace state_str="" if state_str=="."|county_str=="."
replace county_str="" if county_str=="."|state_str==""

gen county=state_str+county_str
destring county,replace
drop state_str county_str state_code county_code

gen year = floor(action_date/10000)
gen miny = floor((action_date-year*10000)/100)
gen month= ym(year,miny)
format month %tm

gen quarter=qofd(dofm(month))
format quarter %tq
 
drop month miny action_date 

save lar`yr',replace
}



* ==============================================================================
* Match loans to lenders
* Merge CHMDA loan-level data with CHMDA TS data using agency_code and respondent_id
* ==============================================================================

* merge with CHMDA-TS
foreach yr of numlist 2007/2017{

use lar`yr',clear

merge m:1 year agency_code respondent_id using ts.dta
keep if _merge==3
drop _merge agency_code respondent*

save lar_ts`yr',replace
}


* ==============================================================================
* Aggregate loan-level to county-level
* ==============================================================================

foreach yr of numlist 2007/2017{

use lar_ts`yr',clear

keep quarter county loan_purpose loan_amount BMPS_FPSVclass nonb nbnf

drop if county==.

gen refi=(loan_purpose==3)

  * Fintech classification
gen finclass = BMPS_FPSVclass

sort quarter county
by quarter county:  gen totn=_N
by quarter county: egen tot=sum(loan_amount)
by quarter county: egen fin=sum(finclass*loan_amount)
by quarter county: egen nonbtot=sum(nonb*loan_amount)
by quarter county: egen nbnftot=sum(nbnf*loan_amount)

gen finshare  = fin/tot
gen nonbshare = nonbtot/tot

sort quarter county
by quarter county: egen totrefi=sum(refi*loan_amount)
by quarter county: egen finrefi=sum(finclass*refi*loan_amount)
by quarter county: egen nonbrefi=sum(nonb*refi*loan_amount)
by quarter county: egen nbnfrefi=sum(nbnf*refi*loan_amount)

gen share_refi = totrefi/tot

gen finshare_refi  = finrefi/totrefi
gen nonbshare_refi = nonbrefi/totrefi
gen nbnfshare_refi = nbnfrefi/totrefi
gen bankshare_refi = 1-nonbshare_refi

by quarter county: egen totpur=sum((1-refi)*loan_amount)
by quarter county: egen finpur=sum(finclass*(1-refi)*loan_amount)
gen finshare_pur = finpur/totpur

by quarter county: keep if _n==1
keep quarter county totn tot *share* fin totrefi finrefi totpur finpur

xtset county quarter
save county`yr',replace
}


clear
* county-year data
foreach yr of numlist 2007/2017{
append using county`yr'
}
save county,replace
